package com.itlong.cloud.controller.expenses;

import com.fasterxml.jackson.core.type.TypeReference;
import com.itlong.cloud.POJO.DTO.BaseDTO;
import com.itlong.cloud.POJO.DTO.common.PadFaceSyncDataDTO;
import com.itlong.cloud.POJO.DTO.property.*;
import com.itlong.cloud.POJO.VO.MessageVO;
import com.itlong.cloud.POJO.VO.property.*;
import com.itlong.cloud.PlatformConstants;
import com.itlong.cloud.annotation.AuthAccessRequired;
import com.itlong.cloud.annotation.LoggerInfo;
import com.itlong.cloud.constants.LogDescConstant;
import com.itlong.cloud.enumerate.FileCatalogEnum;
import com.itlong.cloud.enumerate.ecode.BaseErrorCodeEnum;
import com.itlong.cloud.enumerate.ecode.ErrorCode;
import com.itlong.cloud.enumerate.ecode.PropertyErrorCodeEnum;
import com.itlong.cloud.equip.property.IPadFaceSyncDataServiceClient;
import com.itlong.cloud.iottp.IIotTpJPushClientServiceClient;
import com.itlong.cloud.project.property.IPropertyAccountServiceClient;
import com.itlong.cloud.project.property.IPropertyPaymentManageServiceClient;
import com.itlong.cloud.thrown.DataAccessException;
import com.itlong.cloud.utils.date.DateUtil;
import com.itlong.cloud.utils.file.UploadFileUtil;
import com.itlong.cloud.utils.network.http.HttpProtocolUtil;
import com.itlong.cloud.utils.node.JsonUtil;
import com.itlong.cloud.utils.page.Page;
import com.itlong.cloud.utils.words.StringHandlerUtil;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.slf4j.Logger;import org.slf4j.LoggerFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URI;
import java.net.URLEncoder;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

/**
 * <desc>
 *      物业缴费数据（抄表费、物业费）录入控制器
 * </desc>
 *
 * @createDate 2018/08/22
 */
@RestController
@RequestMapping("/expenses")
@RefreshScope
public class PropertyPaymentManageController {

    private static final Logger LOG = LoggerFactory.getLogger(PropertyPaymentManageController.class);

    @Autowired
    IPropertyPaymentManageServiceClient iPropertyPaymentManageServiceClient;

    @Autowired
    IPadFaceSyncDataServiceClient iPadFaceSyncDataServiceClient;

    @Autowired
    IIotTpJPushClientServiceClient jPushClientServiceClient;
    @Value("${" + PlatformConstants.FILE_FTP_HOST + "}")
    public String FILE_FTP_HOST;

    @Value("${" + PlatformConstants.FILE_FTP_PORT + "}")
    public String FILE_FTP_PORT;

    @Value("${" + PlatformConstants.FILE_FTP_USERNAME + "}")
    public String FILE_FTP_USERNAME;

    @Value("${" + PlatformConstants.FILE_FTP_PASSWORD + "}")
    public String FILE_FTP_PASSWORD;

    @Value("${" + PlatformConstants.FILE_ACCESS_URL + "}")
    public String FILE_ACCESS_URL;

    @Value("${" + PlatformConstants.PROJECT_STATIC_FILE_PATH + "}")
    public String PROJECT_STATIC_FILE_PATH;

    @Autowired
    private IPropertyAccountServiceClient iPropertyAccountServiceClient;

    private static ExecutorService jPushThreadPool = Executors.newFixedThreadPool(5); // 同步数据线程池

    //极光推送国内
    @Autowired
    IIotTpJPushClientServiceClient iIotTpJPushClientServiceClient;
    /**
     * <desc>
     *      获取抄表数据分页列表
     * <desc/>
     *
     * @param pageDTO
     * @return  MessageVO 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/15
     */
    @ResponseBody
    @AuthAccessRequired
    @PostMapping(path = "/dataRecord/getMeterInfoByPage")
    public Object getMeterInfoByPage(PropertyPaymentPageDTO pageDTO) throws Exception {
        try {
            HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
            pageDTO = HttpProtocolUtil.parseRequestParamToDTO(PropertyPaymentPageDTO.class, request);
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode(),
                    iPropertyPaymentManageServiceClient.getMeterInfoByPage(pageDTO));
        } catch (Exception e) {
            throw new DataAccessException("【物业_收费管理_抄表费】获取抄表数据分页列表失败", e);
        }
    }

    /**
     * <desc>
     *      保存抄表或物业费
     * <desc/>
     *
     * @param dto
     * @return  MessageVO 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/15
     */
    @ResponseBody
    @AuthAccessRequired
    @LoggerInfo(operateDesc = LogDescConstant.METER_INFO_SAVE_LOG_DESC)
    @PostMapping(path = "/dataRecord/saveMeterInfo")
    public Object saveMeterInfo(PropertySaveMeterInfoDTO dto,String costs) throws Exception {
        try {
            HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
            dto = HttpProtocolUtil.parseRequestParamToDTO(PropertySaveMeterInfoDTO.class, request);
            costs = request.getAttribute("costs") == null ? null : request.getAttribute("costs").toString();
            if(DateUtil.formatStringToDate(dto.getMonth(),"yyyy-MM").getTime() >=
                    DateUtil.formatStringToDate(DateUtil.fomatDate(new Date(),"yyyy-MM"),"yyyy-MM").getTime()){
                return new MessageVO(PropertyErrorCodeEnum.PROPERTY_PAYMENT_MONTH_ERROR.getErrorCode());
            }
            if(StringUtils.isNotBlank(costs)){
                dto.setFees(JsonUtil.decode(costs, new TypeReference<List<PropertySaveMeterInfoSubDTO>>(){}));
            }
            //查询房间下是否有住户
            if(iPropertyPaymentManageServiceClient.checkRoomHaveOwner(dto.getRoomId()) == 0){
                return new MessageVO(PropertyErrorCodeEnum.ROOM_NOT_FIND_OWNER.getErrorCode());
            }
            //如果为物业费检测房间面积
            if(dto.getSaveType().equals("2")&&!(iPropertyPaymentManageServiceClient.checkRoomArea(dto.getRoomId()) > 0)){
                return new MessageVO(PropertyErrorCodeEnum.PROPERTY_PAYMENT_ROOM_AREA_ERROR.getErrorCode());
            }
            String result = iPropertyPaymentManageServiceClient.saveOrUpdateMemterInfo(dto);
            //修改了过期时间需要重新计算,并极光推送
//            List<PropertyArrearageBillGetVO> propertyArrearageBillGetVOList = iPropertyAccountServiceClient.statisticsPenaltyForProperty(dto.getProjectId());
//            if (propertyArrearageBillGetVOList != null && !propertyArrearageBillGetVOList.isEmpty()) {
//                jPushThreadPool.execute(() -> {
//                    List<String> alreadyPushed = new ArrayList<>();
//                    Map<String, String> comands = new HashMap<>();
//                    comands.put("refreshSips", "1");
//                    for (PropertyArrearageBillGetVO propertyArrearageBillGetVO : propertyArrearageBillGetVOList) {
//                        String projectIdAndPhone = propertyArrearageBillGetVO.getProjectId() + "," + propertyArrearageBillGetVO.getPhoneNum();
//                        if (!alreadyPushed.contains(projectIdAndPhone)) {
//                            try {
//                                iIotTpJPushClientServiceClient.pushWithTaskTO(propertyArrearageBillGetVO.getProjectId(), propertyArrearageBillGetVO.getPhoneNum(), comands, "欠费联动app功能限制变更");
//                                alreadyPushed.add(projectIdAndPhone);
//                            } catch (Exception e) {
//                                e.printStackTrace();
//                            }
//                        }
//                    }
//                });
//            }
            PadFaceSyncDataDTO padFaceSyncDataDTO = new PadFaceSyncDataDTO();
            padFaceSyncDataDTO.setOperateType("0");
            padFaceSyncDataDTO.setRoomId(dto.getRoomId());
            padFaceSyncDataDTO.setProjectId(dto.getProjectId());
            iPadFaceSyncDataServiceClient.userOrFloorSync(padFaceSyncDataDTO);
            if(result.equals("1")){
                return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode());
            }else if(result.equals("-1")){
                return new MessageVO(PropertyErrorCodeEnum.BILL_IS_EXISTS.getErrorCode());
            }else{
                return new MessageVO(BaseErrorCodeEnum.ERROR.getErrorCode(),result);
            }
        } catch (Exception e) {
            throw new DataAccessException("【物业_收费管理_保存抄表或物业费】保存抄表或物业费失败", e);
        }
    }


    /**
     * <desc>
     *      删除录入数据
     * <desc/>
     *
     * @param feeDataId
     * @return  MessageVO 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/15
     */
    @ResponseBody
    @AuthAccessRequired
    @PostMapping(path = "/dataRecord/deleteMeterInfo")
    @LoggerInfo(operateDesc = LogDescConstant.METER_INFO_DELETE_LOG_DESC)
    public Object deleteMeterInfo(String feeDataId,String projectId) throws Exception {
        try {
            HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
            feeDataId = request.getAttribute("feeDataId") == null ? null : request.getAttribute("feeDataId").toString();
            projectId = request.getAttribute("projectId") == null ? null : request.getAttribute("projectId").toString();
            MessageVO messageVO = new MessageVO(feeDataId,BaseErrorCodeEnum.SUCCESS.getErrorCode());
            PropertyGetMeterInfoVO paymentInfoById;
            try {
                paymentInfoById = iPropertyPaymentManageServiceClient.getPaymentInfoById(feeDataId, "1");
            } catch (Exception e) {
                paymentInfoById = iPropertyPaymentManageServiceClient.getPaymentInfoById(feeDataId, "2");
            }
            PadFaceSyncDataDTO padFaceSyncDataDTO = new PadFaceSyncDataDTO();
            padFaceSyncDataDTO.setOperateType("0");
            padFaceSyncDataDTO.setRoomId(paymentInfoById.getRoomId());
            padFaceSyncDataDTO.setProjectId(projectId);
            iPadFaceSyncDataServiceClient.userOrFloorSync(padFaceSyncDataDTO);
            iPropertyPaymentManageServiceClient.deletePaymentRecord(feeDataId);
            //修改了过期时间需要重新计算,并极光推送
//            List<PropertyArrearageBillGetVO> propertyArrearageBillGetVOList = iPropertyAccountServiceClient.statisticsPenaltyForProperty(projectId);
//            if (propertyArrearageBillGetVOList != null && !propertyArrearageBillGetVOList.isEmpty()) {
//                jPushThreadPool.execute(() -> {
//                    List<String> alreadyPushed = new ArrayList<>();
//                    Map<String, String> comands = new HashMap<>();
//                    comands.put("refreshSips", "1");
//                    for (PropertyArrearageBillGetVO propertyArrearageBillGetVO : propertyArrearageBillGetVOList) {
//                        String projectIdAndPhone = propertyArrearageBillGetVO.getProjectId() + "," + propertyArrearageBillGetVO.getPhoneNum();
//                        if (!alreadyPushed.contains(projectIdAndPhone)) {
//                            try {
//                                iIotTpJPushClientServiceClient.pushWithTaskTO(propertyArrearageBillGetVO.getProjectId(), propertyArrearageBillGetVO.getPhoneNum(), comands, "欠费联动app功能限制变更");
//                                alreadyPushed.add(projectIdAndPhone);
//                            } catch (Exception e) {
//                                e.printStackTrace();
//                            }
//                        }
//                    }
//                });
//            }
            return messageVO;
        } catch (Exception e) {
            throw new DataAccessException("【物业_收费管理_删除抄表或物业费录入数据】删除抄表或物业费录入数据失败", e);
        }
    }

    /**
     * <desc>
     *      批量生成抄表费、物业费
     * <desc/>
     *
     * @param buildIds
     * @param month
     * @param type
     * @return  MessageVO 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/15
     */
    @ResponseBody
    @AuthAccessRequired
    @PostMapping(path = "/dataRecord/batchMeterInfo")
    public Object batchMeterInfo(String buildIds,String month,String type) throws Exception {
        try {
            HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
            buildIds = request.getAttribute("buildIds") == null ? null : request.getAttribute("buildIds").toString();
            month = request.getAttribute("month") == null ? null : request.getAttribute("month").toString();
            type = request.getAttribute("type") == null ? null : request.getAttribute("type").toString();
            if(DateUtil.formatStringToDate(month,"yyyy-MM").getTime() >=
                    DateUtil.formatStringToDate(DateUtil.fomatDate(new Date(),"yyyy-MM"),"yyyy-MM").getTime()){
                return new MessageVO(PropertyErrorCodeEnum.PROPERTY_PAYMENT_MONTH_ERROR.getErrorCode());
            }
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode(),
                    iPropertyPaymentManageServiceClient.batchAddPaymentRecord(buildIds, Integer.valueOf(type), month));
        } catch (Exception e) {
            throw new DataAccessException("【物业_收费管理_批量生成抄表费、物业费】批量生成抄表费、物业费失败", e);
        }
    }

    /**
     * <desc>
     *      根据id查询抄表费或物业费
     * <desc/>
     *
     * @param feeDataId
     * @return  MessageVO 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/15
     */
    @ResponseBody
    @AuthAccessRequired
    @PostMapping(path = "/dataRecord/getPaymentInfoById")
    public Object getPaymentInfoById(String feeDataId,String type) throws Exception {
        try {
            HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
            feeDataId = request.getAttribute("feeDataId") == null ? null : request.getAttribute("feeDataId").toString();
            type = request.getAttribute("type") == null ? null : request.getAttribute("type").toString();
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode(),
                    iPropertyPaymentManageServiceClient.getPaymentInfoById(feeDataId, type));
        } catch (Exception e) {
            throw new DataAccessException("【物业_收费管理_根据id查询抄表费或物业费】根据id查询抄表费或物业费失败", e);
        }
    }

    /**
     * <desc>
     *      获取物业费分页列表
     * <desc/>
     *
     * @param pageDTO
     * @return  MessageVO 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/15
     */
    @ResponseBody
    @AuthAccessRequired
    @PostMapping(path = "/dataRecord/getPropertyFeeByPage")
    public Object getPropertyFeeByPage(PropertyPaymentPageDTO pageDTO) throws Exception {
        try {
            HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
            pageDTO = HttpProtocolUtil.parseRequestParamToDTO(PropertyPaymentPageDTO.class, request);
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode(),
                    iPropertyPaymentManageServiceClient.getManagementInfoByPage(pageDTO));
        } catch (Exception e) {
            throw new DataAccessException("【物业_收费管理_获取物业费分页列表】获取物业费分页列表失败", e);
        }
    }


    /**
     * <desc>
     *      导出抄表费模板
     * </desc>
     *
     * @return MessageVO 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/22
     */
    @ResponseBody
    @AuthAccessRequired
    @RequestMapping(path = "/dataRecord/getMeterInfoExcelTemplate", method = RequestMethod.POST)
    public Object getMeterInfoExcelTemplate() {
        MessageVO messageVO;
        try {
            String templateFilePath = FILE_ACCESS_URL + FileCatalogEnum.PROJECT_STATIC_FILE.getType()
                    + PlatformConstants.METER_INFO_TEMPLATE_FILE_NAME;
            URI excelTemplateDownLoadUrl = new URI(templateFilePath);
            messageVO = new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode(), (Object) excelTemplateDownLoadUrl.toString());
        } catch (Exception e) {
            throw new DataAccessException("【物业_收费管理_导出抄表费模板】导出抄表费模板失败", e);
        }
        return messageVO;
    }

    /**
     * <desc>
     *      导出物业费模板
     * </desc>
     *
     * @return MessageVO 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/22
     */
    @ResponseBody
    @AuthAccessRequired
    @RequestMapping(path = "/dataRecord/getPropertyFeeExcelTemplate", method = RequestMethod.POST)
    public Object getPropertyFeeExcelTemplate() {
        MessageVO messageVO;
        try {
            String templateFilePath = FILE_ACCESS_URL + FileCatalogEnum.PROJECT_STATIC_FILE.getType()
                    + PlatformConstants.PROPERTY_FEE_TEMPLATE_FILE_NAME;
            URI excelTemplateDownLoadUrl = new URI(templateFilePath);
            messageVO = new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode(), (Object) excelTemplateDownLoadUrl.toString());
        } catch (Exception e) {
            throw new DataAccessException("【物业_收费管理_导出抄表费模板】导出抄表费模板失败", e);
        }
        return messageVO;
    }

    /**
     * <desc>
     *      导入物业费数据
     * </desc>
     *
     * @return 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/30
     */
    @ResponseBody
    @AuthAccessRequired
    @PostMapping("/dataRecord/importPropertyFee")
    @LoggerInfo(operateDesc = LogDescConstant.PARKING_EXPORT_LOG_DESC)
    public Object importPropertyFee(BaseDTO baseDTO, HttpServletRequest httpServletRequest){
        try {
            baseDTO = HttpProtocolUtil.parseRequestParamToDTO(BaseDTO.class, httpServletRequest);
            if (baseDTO == null) {
                return new MessageVO(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
            }
            if (StringUtils.isBlank(baseDTO.getProjectId())) {
                return new MessageVO(PropertyErrorCodeEnum.PUB_PROJECT_ID_EMPTY_ERR.getErrorCode());
            }
            boolean isMultipart = ServletFileUpload.isMultipartContent(httpServletRequest);
            if (!isMultipart) {
                //是否是带文件上传的表单
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_IMPORT_FILE_LOST_ERR.getErrorCode());
            }
            MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) httpServletRequest;
            Map<String, MultipartFile> fileList = multipartRequest.getFileMap();
            if (fileList.size() < 1) {
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_IMPORT_FILE_LOST_ERR.getErrorCode());
            }
            if (fileList.size() > 1) {
                //目前仅支持一次处理单个文件
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_EXCEL_FILE_TOO_MANY_ERR.getErrorCode());
            }
            List<HSSFSheet> availableSheets = new ArrayList<>(); //合法的Sheet页
            //获取Excel文件
            Map.Entry<String, MultipartFile> fileEntry = fileList.entrySet().iterator().next();
            MultipartFile excelFile = fileEntry.getValue();
            String excelFileName = excelFile.getOriginalFilename();
            // 获取上传文件的类型
            String excelContentType = StringHandlerUtil.copySubStr(excelFileName, excelFileName.lastIndexOf('.'), excelFileName.length());
            if (!(".xls".equalsIgnoreCase(excelContentType) || ".xlsx".equalsIgnoreCase(excelContentType))) {
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_IMPORT_FILE_TYPE_ERR.getErrorCode());
            }
            List<PropertyPropertyFeeRowDTO> errorImportDataList;
            List<PropertySaveMeterInfoDTO> propertySaveMeterInfoDTOs;
            //创建Excel操作对象，07之前版本(*.xls)使用HSSFWorkBook 07之后版本使用（*.xlsx）XSSFWorkbook
            POIFSFileSystem fileSystem = new POIFSFileSystem(excelFile.getInputStream());
            try {
                try (HSSFWorkbook workbook = new HSSFWorkbook(fileSystem)) {
                    //对导入文件的数据格式等基本信息进行合法性初步检测
                    MessageVO messageVO = this.checkImportPropertyFeeFileValid(workbook, availableSheets, PlatformConstants.PROPERTY_FEE_EXCEL_COLUMN_TITLES);
                    if (!messageVO.getMsgCode().equals(BaseErrorCodeEnum.SUCCESS.getErrorCode().getMainCode())) {
                        return messageVO;
                    }
                    //处理Excel文件中的物业费数据
                    propertySaveMeterInfoDTOs = new ArrayList<>();
                    errorImportDataList = this.getImportPropertyFeeFromFile(propertySaveMeterInfoDTOs, availableSheets, baseDTO.getProjectId());
                }
            } finally {
                fileSystem.close();
            }

            //若有非法数据，返回非法数据
            if (!errorImportDataList.isEmpty()) {
                return new MessageVO(PropertyErrorCodeEnum.IMPORT_FILE_INFO_INVALID_ERR.getErrorCode(), errorImportDataList);
            }
            propertySaveMeterInfoDTOs.get(0).setProjectId(baseDTO.getProjectId());
            iPropertyPaymentManageServiceClient.importSavePropertyFees(propertySaveMeterInfoDTOs);

            // 等待1秒再返回前端，一定程度上缓解前端导入成功后马上查询时尚无数据的情况
            Thread.sleep(1000);
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode());
        } catch (Exception e) {
            return new MessageVO(PropertyErrorCodeEnum.IMPORT_EXCEL_ERROR.getErrorCode());
        }
    }

    /**
     * <desc>
     *      从可用的Sheet页中解析车位信息
     * </desc>
     *
     * @param propertySaveMeterInfoDTOs 从Excel(*.xls)导入地址信息数据传输对象(车位信息,用于保存到数据库)
     * @param availableSheets 合法可用到Sheet页
     * @return 错误的车位信息及其错误原因
     * @author Jiaqi.X
     * @createDate 2018/08/30
     */
    private List<PropertyPropertyFeeRowDTO> getImportPropertyFeeFromFile(List<PropertySaveMeterInfoDTO> propertySaveMeterInfoDTOs, List<HSSFSheet> availableSheets ,String projectId) throws Exception {
        // Excel单元格数据操作对象
        DataFormatter dataFormatter = new DataFormatter();
        // 错误的行信息列表
        List<PropertyPropertyFeeRowDTO> errorImportDataList = new ArrayList<>();
        //遍历所有可用Sheet，逐行处理每个Sheet的数据
        for (HSSFSheet oneSheet : availableSheets) {
            int rowsCountOfCurrentSheet = oneSheet.getPhysicalNumberOfRows();
            for (int rowIndex = 1; rowIndex < rowsCountOfCurrentSheet; rowIndex++) {
                HSSFRow row = oneSheet.getRow(rowIndex);
                if(row!=null) {
                    //对当前Sheet中的当前行进行数据处理
                    PropertyPropertyFeeRowDTO propertyPropertyFeeRowDTO = handleOneRowAddressInfo(row, propertySaveMeterInfoDTOs, dataFormatter, projectId);
                    //若存在错误信息，则将加入错误列表
                    if (propertyPropertyFeeRowDTO != null && !propertyPropertyFeeRowDTO.getErrorCodes().isEmpty()) {
                        this.addErrorPropertyFeeInfoToList(errorImportDataList, propertyPropertyFeeRowDTO);
                    }
                }
            }
        }
        return errorImportDataList;
    }

    /**
     * <desc>
     *      根据Excel的一行数据获取单条"物业费信息及其错误码列表".
     * </desc>
     *
     * @param hssfRow Excel的一行数据
     * @param saveList 从Excel(*.xls)导入地址信息数据传输对象(物业费信息,用于保存到数据库)
     * @param dataFormatter Excel单元格数据的读取器
     * @return 错误码列表
     * @author Jiaqi.X
     * @createDate 2018/08/30
     */
    private PropertyPropertyFeeRowDTO  handleOneRowAddressInfo(HSSFRow hssfRow, List<PropertySaveMeterInfoDTO> saveList, DataFormatter dataFormatter,String projectId) throws Exception {
        PropertyPropertyFeeRowDTO propertyPropertyFeeRowDTO = new PropertyPropertyFeeRowDTO();
        PropertySaveMeterInfoDTO propertySaveMeterInfoSaveDTO = new PropertySaveMeterInfoDTO();
        List<ErrorCode> errorCodes = propertyPropertyFeeRowDTO.getErrorCodes();
        //首先登记所属的Sheet名称和行号
        propertyPropertyFeeRowDTO.setSheetName(hssfRow.getSheet().getSheetName());
        propertyPropertyFeeRowDTO.setRowNum(hssfRow.getRowNum() + 1);
        //处理收费月份
        String month = dataFormatter.formatCellValue(hssfRow.getCell(0));
        if (StringUtils.isBlank(month)) {
            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
        } else if(!month.matches("[1-9]+\\d{3}\\-(?:0[1-9]|1[0-2]|[1-9]{1})")){
            errorCodes.add(PropertyErrorCodeEnum.PROPERTY_PAYMENT_MONTH_FORMAT_ERROR.getErrorCode());
            propertyPropertyFeeRowDTO.setMonth(month);
            propertySaveMeterInfoSaveDTO.setMonth(month);
        }else if(DateUtil.formatStringToDate(month,"yyyy-MM").getTime() >=
                DateUtil.formatStringToDate(DateUtil.fomatDate(new Date(),"yyyy-MM"),"yyyy-MM").getTime()) {
            errorCodes.add(PropertyErrorCodeEnum.PROPERTY_PAYMENT_MONTH_ERROR.getErrorCode());
            propertyPropertyFeeRowDTO.setMonth(month);
            propertySaveMeterInfoSaveDTO.setMonth(month);
        }else{
            try {
                propertyPropertyFeeRowDTO.setMonth(DateUtil.fomatDate(DateUtil.formatStringToDate(month,"yyyy-MM"),"yyyy-MM"));
                propertySaveMeterInfoSaveDTO.setMonth(DateUtil.fomatDate(DateUtil.formatStringToDate(month,"yyyy-MM"),"yyyy-MM"));
            }catch (Exception e){
                errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
            }
        }
        if(StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(0)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(1)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(2)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(3)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(4)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(5)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(6)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(7)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(8)))
                ){
            return null;
        }
        // 处理地址
        String address = dataFormatter.formatCellValue(hssfRow.getCell(1));
        if (StringUtils.isBlank(address)) {
            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
        } else{
            address.replaceAll(" ","");
            if(!address.matches(".*-.*-.*")){
                errorCodes.add(PropertyErrorCodeEnum.PROPERTY_PAYMEN_ADDRESS_ERROR.getErrorCode());
            }else {
                //查询房间是否存在及是否有业主
                Integer check = iPropertyPaymentManageServiceClient.checkRoomExists(address, projectId);
                if (check == 1) {
                    errorCodes.add(PropertyErrorCodeEnum.ROOM_IS_NOT_EXISTS.getErrorCode());
                } else if (check == 2) {
                    errorCodes.add(PropertyErrorCodeEnum.ROOM_NOT_FIND_OWNER.getErrorCode());
                }else if(check == 3){
                    errorCodes.add(PropertyErrorCodeEnum.ROOM_AREA_IS_NULL.getErrorCode());
                }
            }
        }
        propertyPropertyFeeRowDTO.setAddress(address);
        propertySaveMeterInfoSaveDTO.setAddress(address);
        //处理收费规则
        String rule = dataFormatter.formatCellValue(hssfRow.getCell(4));
        if (StringUtils.isBlank(rule)) {
            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
        } else {
            //判断规则是否存在
            if(iPropertyPaymentManageServiceClient.checkRuleIsExists(projectId, rule,2)){
                errorCodes.add(PropertyErrorCodeEnum.RULE_IS_NOT_EXISTS.getErrorCode());
            }else {
                if (address.matches(".*-.*-.*")) {
                    if (iPropertyPaymentManageServiceClient.checkImportDataRuleTime(address, rule, 2, projectId, month) != 0) {
                        errorCodes.add(PropertyErrorCodeEnum.BILL_IS_EXISTS.getErrorCode());
                    }
                }
            }
        }
        propertyPropertyFeeRowDTO.setRule(rule);
        propertySaveMeterInfoSaveDTO.setRule(rule);
        // 处理备注
        String remark = dataFormatter.formatCellValue(hssfRow.getCell(5));
        remark = remark.trim();
        propertyPropertyFeeRowDTO.setRemark(remark);
        propertySaveMeterInfoSaveDTO.setRemark(remark);
        saveList.add(propertySaveMeterInfoSaveDTO);
        return propertyPropertyFeeRowDTO;
    }


    /**
     * <desc>
     *     向错误列表中增加有错误的物业费信息
     * </desc>
     *
     * @param  errorImportDataList 错误列表
     * @param  propertyParkingRowDTO 有错误的车位信息
     * @author Jiaqi.X
     * @createDate 2018/08/17
     */
    private void addErrorPropertyFeeInfoToList(List<PropertyPropertyFeeRowDTO> errorImportDataList, PropertyPropertyFeeRowDTO propertyParkingRowDTO) {
        for (PropertyPropertyFeeRowDTO oneAddressInfo:errorImportDataList){
            if ((oneAddressInfo.getRowNum() ==  propertyParkingRowDTO.getRowNum())
                    && (StringHandlerUtil.compareStrWithOutEmpty(oneAddressInfo.getSheetName(),propertyParkingRowDTO.getSheetName()))){
                return;
            }
        }
        errorImportDataList.add(propertyParkingRowDTO);
    }

    /**
     * <desc>
     *      对导入文件的文件格式等基本信息进行合法性初步检测
     * </desc>
     *
     * @param workbook Excel操作对象
     * @param availableSheets 合法可用到Sheet页
     * @return MessageVO 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/17
     */
    private MessageVO checkImportPropertyFeeFileValid(HSSFWorkbook workbook, List<HSSFSheet> availableSheets,String[] titles) {
        //获取Excel的Sheet页面数，每个Excel文件至少会有一个Sheet页(Excel文件本身到限定)
        int sheetCount = workbook.getNumberOfSheets();
//        int totalRowsCount = 0;
        //遍历当前文件的所有Sheet页，进行合法性检测
        for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
            HSSFSheet oneSheet = workbook.getSheetAt(sheetIndex);
            int rowsCountOfCurrentSheet = oneSheet.getPhysicalNumberOfRows();
//            totalRowsCount += rowsCountOfCurrentSheet;
            if (rowsCountOfCurrentSheet < 1) {
                continue;
            }
            //对当前Sheet的列数量进行合法性判断
            HSSFRow headerRow = oneSheet.getRow(0);
            int columnCount = oneSheet.getRow(0).getPhysicalNumberOfCells();
            if (columnCount != titles.length) {
                return new MessageVO(PropertyErrorCodeEnum.PROPERTY_FEE_EXCEL_COLUMN_COUNT_MISMATCH_ERR.getErrorCode());
            }
            //每个Sheet页的第一页为列标题，进行标题头名称进行合法性判断
            DataFormatter dataFormatter = new DataFormatter();
            for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) {
                String oneFieldValue = dataFormatter.formatCellValue(headerRow.getCell(columnIndex));

                String columnTitle = titles[columnIndex];
                if (!StringHandlerUtil.compareStrWithOutEmpty(oneFieldValue, columnTitle)) {
                    ErrorCode errorCode = PropertyErrorCodeEnum.PROPERTY_FEE_EXCEL_COLUMN_TITLE_MISMATCH_ERR.getErrorCode();
                    return new MessageVO(errorCode,
                            String.format(errorCode.getCodeMsg(), oneFieldValue, columnTitle));
                }
            }
            //至少有一行实际数据可导入才加入Sheet处理列表
            if (rowsCountOfCurrentSheet > 1) {
                availableSheets.add(oneSheet);
            }
        }
        //没有数据
        if (availableSheets.isEmpty()) {
            return new MessageVO(PropertyErrorCodeEnum.PROJECT_IMPORT_FILE_NO_DATA_ERR.getErrorCode());
        }
        return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode());
    }

    /**
     * <desc>
     *      Ftp上传文件，返回Url.
     * </desc>
     *
     * @param inputStream 文件流
     * @param fileName 文件名称
     * @return 文件上传成功后会返回Url,否则返回空
     * @author Jiaqi.X
     * @createDate 2018/08/17
     */
    private String uploadFile(InputStream inputStream, String fileName) throws IOException {
        String fileUrl = "";
        String fileType = StringHandlerUtil.copySubStr(fileName, fileName.lastIndexOf('.') + 1, fileName.length());
        UploadFileUtil.UploadFile uploadFileUtil = new UploadFileUtil().new UploadFile(
                FILE_FTP_HOST,
                FILE_FTP_USERNAME,
                FILE_FTP_PASSWORD,
                FILE_FTP_PORT,
                inputStream,
                fileType,
                FILE_ACCESS_URL,
                FileCatalogEnum.PROPERTY_METER_INFO_FILE.getType()
        );
        Map<String, Object> resultMap = UploadFileUtil.uploadFile(uploadFileUtil);
        Boolean returnCode = (Boolean)resultMap.get("returnCode");
        if (returnCode == false){
            return fileUrl;
        }
        fileUrl = resultMap.get("accessPath").toString();
        return fileUrl;
    }

    /**
     * <desc>
     *      导出物业费
     * </desc>
     *
     * @param dto
     * @return 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/30
     */
    @ResponseBody
    @AuthAccessRequired
    @PostMapping("/dataRecord/exportPropertyFee")
    public Object exportPropertyFee(BaseDTO baseDTO,PropertyPaymentPageDTO dto){
        try {
            HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
            baseDTO = HttpProtocolUtil.parseRequestParamToDTO(BaseDTO.class, request);
            dto = HttpProtocolUtil.parseRequestParamToDTO(PropertyPaymentPageDTO.class, request);
            if (StringUtils.isBlank(dto.getProjectId())) {
                return new MessageVO(PropertyErrorCodeEnum.PUB_PROJECT_ID_EMPTY_ERR.getErrorCode());
            }
            dto.setExport("1");
            Page<PropertyGetManagementPageVO> vos = iPropertyPaymentManageServiceClient.getManagementInfoByPage(dto);
            if (vos.getRows() == null || vos.getRows().isEmpty()) {
                return new MessageVO(PropertyErrorCodeEnum.EXPORT_DATA_EMPTY_ERR.getErrorCode());
            }
            String[] title = {"收费月份","地址","住户","手机号","收费规则","备注"};
            short rowHeight= 420;
            //创建excel工作簿
            HSSFWorkbook workbook=new HSSFWorkbook();
            //创建工作表sheet并设置简单格式
            HSSFSheet sheet=workbook.createSheet();
            sheet.setDefaultColumnWidth(15);
            //创建第一行
            HSSFRow row=sheet.createRow(0);
            row.setHeight(rowHeight);
            HSSFCell cell=null;
            //插入第一行数据的表头
            for (int i = 0; i < title.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(title[i]);
            }
            //写入数据
            for (int index = 0; index < vos.getRows().size(); index++) {
                PropertyGetManagementPageVO vo = vos.getRows().get(index);
                int rowIndex = index + 1; // 第一行已为标题行
                HSSFRow dataRow = sheet.createRow(rowIndex);
                dataRow.setHeight(rowHeight);
                HSSFCell noteCell = dataRow.createCell(0);
                noteCell.setCellValue(vo.getMonth());
                noteCell = dataRow.createCell(1);
                noteCell.setCellValue(vo.getAddress());
                noteCell = dataRow.createCell(2);
                noteCell.setCellValue(vo.getRealName());
                noteCell = dataRow.createCell(3);
                noteCell.setCellValue(vo.getPhoneNum());
                noteCell = dataRow.createCell(4);
                noteCell.setCellValue(vo.getFeeRuleName());
                noteCell = dataRow.createCell(5);
                noteCell.setCellValue(vo.getRemark());
            }
            String fileName = String.format("%s_PropertyFeeInfo_%s.xls",
                    baseDTO.getProjectId(),
                    DateFormatUtils.format(new Date(), "yyyyMMddHHmmssSSS"));
            fileName = URLEncoder.encode(fileName, "UTF-8");
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            byte[] content = os.toByteArray();
            InputStream exportFileStream = new ByteArrayInputStream(content);
            String exportFileUrl = uploadFile(exportFileStream,fileName);
            if (StringUtils.isBlank(exportFileUrl)){
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_EXPORT_FILE_SAVE_ERR.getErrorCode());
            }
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode(), (Object) exportFileUrl);
        } catch (Exception e) {
            throw new DataAccessException("【物业-物业缴费】导出物业费表单失败",e);
        }
    }




    /**
     * <desc>
     *      导入抄表费数据
     * </desc>
     *
     * @return 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/30
     */
    @ResponseBody
    @AuthAccessRequired
    @PostMapping("/dataRecord/importMeterInfos")
    public Object importMeterInfos(BaseDTO baseDTO, HttpServletRequest httpServletRequest){
        try {
            baseDTO = HttpProtocolUtil.parseRequestParamToDTO(BaseDTO.class, httpServletRequest);
            if (baseDTO == null) {
                return new MessageVO(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
            }
            if (StringUtils.isBlank(baseDTO.getProjectId())) {
                return new MessageVO(PropertyErrorCodeEnum.PUB_PROJECT_ID_EMPTY_ERR.getErrorCode());
            }
            boolean isMultipart = ServletFileUpload.isMultipartContent(httpServletRequest);
            if (!isMultipart) {
                //是否是带文件上传的表单
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_IMPORT_FILE_LOST_ERR.getErrorCode());
            }
            MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) httpServletRequest;
             Map<String, MultipartFile> fileList = multipartRequest.getFileMap();
            if (fileList.size() < 1) {
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_IMPORT_FILE_LOST_ERR.getErrorCode());
            }
            if (fileList.size() > 1) {
                //目前仅支持一次处理单个文件
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_EXCEL_FILE_TOO_MANY_ERR.getErrorCode());
            }
            List<HSSFSheet> availableSheets = new ArrayList<>(); //合法的Sheet页
            //获取Excel文件
            Map.Entry<String, MultipartFile> fileEntry = fileList.entrySet().iterator().next();
            MultipartFile excelFile = fileEntry.getValue();
            String excelFileName = excelFile.getOriginalFilename();
            // 获取上传文件的类型
            String excelContentType = StringHandlerUtil.copySubStr(excelFileName, excelFileName.lastIndexOf('.'), excelFileName.length());
            if (!(".xls".equalsIgnoreCase(excelContentType) || ".xlsx".equalsIgnoreCase(excelContentType))) {
                return new MessageVO(PropertyErrorCodeEnum.PROPERTY_FEE_EXCEL_COLUMN_COUNT_MISMATCH_ERR.getErrorCode());
            }
            List<PropertyMeterInfoRowDTO> errorImportDataList;
            List<PropertySaveMeterInfoDTO> propertySaveMeterInfoDTOs;
            //创建Excel操作对象，07之前版本(*.xls)使用HSSFWorkBook 07之后版本使用（*.xlsx）XSSFWorkbook
            POIFSFileSystem fileSystem = new POIFSFileSystem(excelFile.getInputStream());
            try {
                try (HSSFWorkbook workbook = new HSSFWorkbook(fileSystem)) {

                    //对导入文件的数据格式等基本信息进行合法性初步检测
                    MessageVO messageVO = this.checkImportMeterInfoFileValid(workbook, availableSheets, PlatformConstants.METER_INFO_EXCEL_COLUMN_TITLES);
                    if (!messageVO.getMsgCode().equals(BaseErrorCodeEnum.SUCCESS.getErrorCode().getMainCode())) {
                        return messageVO;
                    }
                    //处理Excel文件中的抄表费数据
                    propertySaveMeterInfoDTOs = new ArrayList<>();
                    errorImportDataList = this.getImportMeterInfoFromFile(propertySaveMeterInfoDTOs, availableSheets, baseDTO.getProjectId());
                }
            } finally {
                fileSystem.close();
            }

            //若有非法数据，返回非法数据
            if (!errorImportDataList.isEmpty()) {
                return new MessageVO(PropertyErrorCodeEnum.IMPORT_FILE_INFO_INVALID_ERR.getErrorCode(), errorImportDataList);
            }
            propertySaveMeterInfoDTOs.get(0).setProjectId(baseDTO.getProjectId());
            String result = iPropertyPaymentManageServiceClient.importSaveMeterInfoFees(propertySaveMeterInfoDTOs);
            if(StringUtils.isNotBlank(result)){
                return new MessageVO(PropertyErrorCodeEnum.IMPORT_METER_INFO_TYPES_ERROR.getErrorCode(),result+"房间导入抄表数据错误，缺失导入类型，需一次性导入房间水费、电费、燃气费类型的数据,且同月份房间下收费规则周期一致");
            }
            // 等待1秒再返回前端，一定程度上缓解前端导入成功后马上查询时尚无数据的情况
            Thread.sleep(1000);
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode());
        } catch (Exception e) {
            LOG.error("导入抄表费失败："+e.getMessage());
            return new MessageVO(PropertyErrorCodeEnum.IMPORT_EXCEL_ERROR.getErrorCode());

        }
    }

    /**
     * <desc>
     *      从可用的Sheet页中解析抄表费信息
     * </desc>
     *
     * @param propertySaveMeterInfoDTOs 从Excel(*.xls)导入地址信息数据传输对象(车位信息,用于保存到数据库)
     * @param availableSheets 合法可用到Sheet页
     * @return 错误的车位信息及其错误原因
     * @author Jiaqi.X
     * @createDate 2018/08/30
     */
    private List<PropertyMeterInfoRowDTO> getImportMeterInfoFromFile(List<PropertySaveMeterInfoDTO> propertySaveMeterInfoDTOs, List<HSSFSheet> availableSheets ,String projectId) throws Exception {
        // Excel单元格数据操作对象
        DataFormatter dataFormatter = new DataFormatter();
        // 错误的行信息列表
        List<PropertyMeterInfoRowDTO> errorImportDataList = new ArrayList<>();
        //遍历所有可用Sheet，逐行处理每个Sheet的数据
        for (HSSFSheet oneSheet : availableSheets) {
            int rowsCountOfCurrentSheet = oneSheet.getPhysicalNumberOfRows();
            for (int rowIndex = 1; rowIndex < rowsCountOfCurrentSheet; rowIndex++) {
                HSSFRow row = oneSheet.getRow(rowIndex);
                if(row!=null) {
                    //对当前Sheet中的当前行进行数据处理
                    PropertyMeterInfoRowDTO propertyPropertyFeeRowDTO = handleOneRowAddressMeterInfo(row, propertySaveMeterInfoDTOs, dataFormatter, projectId);
                    //若存在错误信息，则将加入错误列表
                    if (propertyPropertyFeeRowDTO != null && !propertyPropertyFeeRowDTO.getErrorCodes().isEmpty()) {
                        this.addErrorMeterInfoToList(errorImportDataList, propertyPropertyFeeRowDTO);
                    }
                }
            }
        }
        return errorImportDataList;
    }

    /**
     * <desc>
     *      根据Excel的一行数据获取单条"物业费信息及其错误码列表".
     * </desc>
     *
     * @param hssfRow Excel的一行数据
     * @param saveList 从Excel(*.xls)导入地址信息数据传输对象(物业费信息,用于保存到数据库)
     * @param dataFormatter Excel单元格数据的读取器
     * @return 错误码列表
     * @author Jiaqi.X
     * @createDate 2018/08/30
     */
    private PropertyMeterInfoRowDTO  handleOneRowAddressMeterInfo(HSSFRow hssfRow, List<PropertySaveMeterInfoDTO> saveList, DataFormatter dataFormatter,String projectId) throws Exception {
        PropertyMeterInfoRowDTO propertyMeterInfoRowDTO = new PropertyMeterInfoRowDTO();
        PropertySaveMeterInfoDTO propertySaveMeterInfoSaveDTO = new PropertySaveMeterInfoDTO();
        List<ErrorCode> errorCodes = propertyMeterInfoRowDTO.getErrorCodes();
        //首先登记所属的Sheet名称和行号
        propertyMeterInfoRowDTO.setSheetName(hssfRow.getSheet().getSheetName());
        propertyMeterInfoRowDTO.setRowNum(hssfRow.getRowNum() + 1);
        if(StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(0)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(1)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(2)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(3)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(4)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(5)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(6)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(7)))&&
                StringUtils.isBlank(dataFormatter.formatCellValue(hssfRow.getCell(8)))
                ){
            return null;
        }
        //处理抄表月份
        String month = dataFormatter.formatCellValue(hssfRow.getCell(0));
        if (StringUtils.isBlank(month)) {
            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
        } else if (!month.matches("[1-9]+\\d{3}\\-(?:0[1-9]|1[0-2]|[1-9]{1})")){
            errorCodes.add(PropertyErrorCodeEnum.PROPERTY_PAYMENT_MONTH_FORMAT_ERROR.getErrorCode());
            propertyMeterInfoRowDTO.setMonth(month);
            propertySaveMeterInfoSaveDTO.setMonth(month);
        }else if( DateUtil.formatStringToDate(month,"yyyy-MM").getTime() >=
                DateUtil.formatStringToDate(DateUtil.fomatDate(new Date(),"yyyy-MM"),"yyyy-MM").getTime()){
            errorCodes.add(PropertyErrorCodeEnum.PROPERTY_PAYMENT_MONTH_ERROR.getErrorCode());
            propertyMeterInfoRowDTO.setMonth(month);
            propertySaveMeterInfoSaveDTO.setMonth(month);
        } else {
            try {
                propertyMeterInfoRowDTO.setMonth(DateUtil.fomatDate(DateUtil.formatStringToDate(month,"yyyy-MM"),"yyyy-MM"));
                propertySaveMeterInfoSaveDTO.setMonth(DateUtil.fomatDate(DateUtil.formatStringToDate(month,"yyyy-MM"),"yyyy-MM"));
            }catch (Exception e){
                errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
            }
        }
        //处理地址
        String address = dataFormatter.formatCellValue(hssfRow.getCell(1));
        if (StringUtils.isBlank(address)) {
            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
        }else{
            if(!address.matches(".*-.*-.*")){
                errorCodes.add(PropertyErrorCodeEnum.PROPERTY_PAYMEN_ADDRESS_ERROR.getErrorCode());
            }else {
                //查询房间是否存在及是否有业主
                Integer check = iPropertyPaymentManageServiceClient.checkRoomExists(address, projectId);
                if (check == 1) {
                    errorCodes.add(PropertyErrorCodeEnum.ROOM_IS_NOT_EXISTS.getErrorCode());
                } else if (check == 2) {
                    errorCodes.add(PropertyErrorCodeEnum.ROOM_NOT_FIND_OWNER.getErrorCode());
                }
            }
            propertyMeterInfoRowDTO.setAddress(address);
            propertySaveMeterInfoSaveDTO.setAddress(address);
        }
        //处理抄表类型
        String type = dataFormatter.formatCellValue(hssfRow.getCell(4));
        if (StringUtils.isBlank(type)) {
            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
        } else {
            if(type.equals("水费")){
                propertyMeterInfoRowDTO.setType("1");
                propertySaveMeterInfoSaveDTO.setType("1");
            }else if(type.equals("电费")){
                propertyMeterInfoRowDTO.setType("2");
                propertySaveMeterInfoSaveDTO.setType("2");
            }else if(type.equals("燃气费")){
                propertyMeterInfoRowDTO.setType("3");
                propertySaveMeterInfoSaveDTO.setType("3");
            }else{
                errorCodes.add(PropertyErrorCodeEnum.PROPERTY_METERINFO_TYPE_ERROR.getErrorCode());
                propertySaveMeterInfoSaveDTO.setType(type);
                propertyMeterInfoRowDTO.setType(type);
            }
        }
        //处理抄表始度
        String start = dataFormatter.formatCellValue(hssfRow.getCell(5));
        if (StringUtils.isBlank(start)) {
            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
        } else {
            propertyMeterInfoRowDTO.setStart(start);
            propertySaveMeterInfoSaveDTO.setStart(start);
        }
        //处理抄表止度
        String end = dataFormatter.formatCellValue(hssfRow.getCell(6));
        if (StringUtils.isBlank(end)) {
            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
        } else {
            propertyMeterInfoRowDTO.setEnd(end);
            propertySaveMeterInfoSaveDTO.setEnd(end);
        }
        //处理损耗
        String lossAmount = dataFormatter.formatCellValue(hssfRow.getCell(7));
        if (StringUtils.isNotBlank(lossAmount)) {
            propertyMeterInfoRowDTO.setLossAmount(lossAmount);
            propertySaveMeterInfoSaveDTO.setLossAmount(lossAmount);
        }
        //处理收费规则
        String rule = dataFormatter.formatCellValue(hssfRow.getCell(8));
        if (StringUtils.isBlank(rule)) {
            errorCodes.add(BaseErrorCodeEnum.NOT_PARAM.getErrorCode());
        } else {
            if(iPropertyPaymentManageServiceClient.checkRuleIsExists(projectId, rule,1)){
                errorCodes.add(PropertyErrorCodeEnum.RULE_IS_NOT_EXISTS.getErrorCode());
            }else {
                if (address.matches(".*-.*-.*")) {
                    if (iPropertyPaymentManageServiceClient.checkImportDataRuleTime(address, rule, 1, projectId, month) == 0) {
                        propertyMeterInfoRowDTO.setRule(rule);
                        propertySaveMeterInfoSaveDTO.setRule(rule);
                    } else {
                        errorCodes.add(PropertyErrorCodeEnum.BILL_IS_EXISTS.getErrorCode());
                    }
                }
            }
        }
        saveList.add(propertySaveMeterInfoSaveDTO);
        return propertyMeterInfoRowDTO;
    }


    /**
     * <desc>
     *     向错误列表中增加有错误的车位信息.
     * </desc>
     *
     * @param  errorImportDataList 错误列表
     * @param  propertyMeterInfoRowDTO 有错误的车位信息
     * @author Jiaqi.X
     * @createDate 2018/08/17
     */
    private void addErrorMeterInfoToList(List<PropertyMeterInfoRowDTO> errorImportDataList, PropertyMeterInfoRowDTO propertyMeterInfoRowDTO) {
        for (PropertyMeterInfoRowDTO oneAddressInfo:errorImportDataList){
            if ((oneAddressInfo.getRowNum() ==  propertyMeterInfoRowDTO.getRowNum())
                    && (StringHandlerUtil.compareStrWithOutEmpty(oneAddressInfo.getSheetName(),propertyMeterInfoRowDTO.getSheetName()))){
                return;
            }
        }
        errorImportDataList.add(propertyMeterInfoRowDTO);
    }

    /**
     * <desc>
     *      对导入文件的文件格式等基本信息进行合法性初步检测
     * </desc>
     *
     * @param workbook Excel操作对象
     * @param availableSheets 合法可用到Sheet页
     * @return MessageVO 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/17
     */
    private MessageVO checkImportMeterInfoFileValid(HSSFWorkbook workbook, List<HSSFSheet> availableSheets,String[] titles) {
        //获取Excel的Sheet页面数，每个Excel文件至少会有一个Sheet页(Excel文件本身到限定)
        int sheetCount = workbook.getNumberOfSheets();
//        int totalRowsCount = 0;
        //遍历当前文件的所有Sheet页，进行合法性检测
        for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
            HSSFSheet oneSheet = workbook.getSheetAt(sheetIndex);
            int rowsCountOfCurrentSheet = oneSheet.getPhysicalNumberOfRows();
//            totalRowsCount += rowsCountOfCurrentSheet;
            if (rowsCountOfCurrentSheet < 1) {
                continue;
            }
            //对当前Sheet的列数量进行合法性判断
            HSSFRow headerRow = oneSheet.getRow(0);
            int columnCount = oneSheet.getRow(0).getPhysicalNumberOfCells();
            if (columnCount != titles.length) {
                return new MessageVO(PropertyErrorCodeEnum.PROPERTY_FEE_EXCEL_COLUMN_COUNT_MISMATCH_ERR.getErrorCode());
            }
            //每个Sheet页的第一页为列标题，进行标题头名称进行合法性判断
            DataFormatter dataFormatter = new DataFormatter();
            for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) {
                String oneFieldValue = dataFormatter.formatCellValue(headerRow.getCell(columnIndex));

                String columnTitle = titles[columnIndex];
                if (!StringHandlerUtil.compareStrWithOutEmpty(oneFieldValue, columnTitle)) {
                    ErrorCode errorCode = PropertyErrorCodeEnum.PROPERTY_FEE_EXCEL_COLUMN_TITLE_MISMATCH_ERR.getErrorCode();
                    return new MessageVO(errorCode,
                            String.format(errorCode.getCodeMsg(), oneFieldValue, columnTitle));
                }
            }
            //至少有一行实际数据可导入才加入Sheet处理列表
            if (rowsCountOfCurrentSheet > 1) {
                availableSheets.add(oneSheet);
            }
        }
        //没有数据
        if (availableSheets.isEmpty()) {
            return new MessageVO(PropertyErrorCodeEnum.PROPERTY_FEE_EXCEL_COLUMN_COUNT_MISMATCH_ERR.getErrorCode());
        }
        return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode());
    }


    /**
     * <desc>
     *      导出抄表费
     * </desc>
     *
     * @param dto
     * @return 标准格式化响应结果
     * @author Jiaqi.X
     * @createDate 2018/08/30
     */
    @ResponseBody
    @AuthAccessRequired
    @PostMapping("/dataRecord/exportMeterInfo")
    public Object exportMeterInfo(BaseDTO baseDTO,PropertyPaymentPageDTO dto){
        try {
            HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
            baseDTO = HttpProtocolUtil.parseRequestParamToDTO(BaseDTO.class, request);
            dto = HttpProtocolUtil.parseRequestParamToDTO(PropertyPaymentPageDTO.class, request);
            if (StringUtils.isBlank(dto.getProjectId())) {
                return new MessageVO(PropertyErrorCodeEnum.PUB_PROJECT_ID_EMPTY_ERR.getErrorCode());
            }
            dto.setExport("1");
            Page<PropertyPaymentGetPageVO> vos = iPropertyPaymentManageServiceClient.getMeterInfoByPage(dto);
            if (vos.getRows() == null || vos.getRows().isEmpty()) {
                return new MessageVO(PropertyErrorCodeEnum.EXPORT_DATA_EMPTY_ERR.getErrorCode());
            }
            String[] title = {"抄表月份","地址","住户","手机号","抄表类型","抄表始度","抄表止度","损耗","收费规则"};
            short rowHeight= 420;
            //创建excel工作簿
            HSSFWorkbook workbook=new HSSFWorkbook();
            //创建工作表sheet并设置简单格式
            HSSFSheet sheet=workbook.createSheet();
            sheet.setDefaultColumnWidth(15);
            //创建第一行
            HSSFRow row=sheet.createRow(0);
            row.setHeight(rowHeight);
            HSSFCell cell=null;
            //插入第一行数据的表头
            for (int i = 0; i < title.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(title[i]);
            }
            //写入数据
            int i = 0;
            for(PropertyPaymentGetPageVO vo : vos.getRows()) {
                for (int index = 0; index < vo.getCosts().size(); index++) {
                    PropertyPaymentGetPageCostsVO cost = vo.getCosts().get(index);
                    int rowIndex = i + 1; // 第一行已为标题行
                    HSSFRow dataRow = sheet.createRow(rowIndex);
                    dataRow.setHeight(rowHeight);
                    HSSFCell noteCell = dataRow.createCell(0);
                    noteCell.setCellValue(vo.getMonth());
                    noteCell = dataRow.createCell(1);
                    noteCell.setCellValue(vo.getAddress());
                    noteCell = dataRow.createCell(2);
                    noteCell.setCellValue(vo.getRealName());
                    noteCell = dataRow.createCell(3);
                    noteCell.setCellValue(vo.getPhoneNum());
                    noteCell = dataRow.createCell(4);
                    switch (cost.getType()){
                        case "1":
                            noteCell.setCellValue("水费");
                            break;
                        case "2":
                            noteCell.setCellValue("电费");
                            break;
                        case "3":
                            noteCell.setCellValue("燃气费");
                    }
                    noteCell = dataRow.createCell(5);
                    noteCell.setCellValue(cost.getStart());
                    noteCell = dataRow.createCell(6);
                    noteCell.setCellValue(cost.getEnd());
                    noteCell = dataRow.createCell(7);
                    noteCell.setCellValue(cost.getLossAmount());
                    noteCell = dataRow.createCell(8);
                    noteCell.setCellValue(cost.getFeeRuleName());
                    i++;
                }
            }
            String fileName = String.format("%s_PropertyMeterInfo_%s.xls",
                    baseDTO.getProjectId(),
                    DateFormatUtils.format(new Date(), "yyyyMMddHHmmssSSS"));
            fileName = URLEncoder.encode(fileName, "UTF-8");
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            byte[] content = os.toByteArray();
            InputStream exportFileStream = new ByteArrayInputStream(content);
            String exportFileUrl = uploadFile(exportFileStream,fileName);
            if (StringUtils.isBlank(exportFileUrl)){
                return new MessageVO(PropertyErrorCodeEnum.PROJECT_EXPORT_FILE_SAVE_ERR.getErrorCode());
            }
            return new MessageVO(BaseErrorCodeEnum.SUCCESS.getErrorCode(), (Object) exportFileUrl);
        } catch (Exception e) {
            throw new DataAccessException("【物业-物业缴费】导出抄表费表单失败",e);
        }
    }
}